<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2023/4/12
 * Time: 12:38
 */

namespace excel;
vendor('PhpExcel.PHPExcel');
vendor('PhpExcel.IOFactory');
use app\index\model\CrmGroup;
use app\index\model\CrmManage;
use app\index\model\CrmOrderGoods;
use app\index\model\CrmOrderMoney;
use app\index\model\CrmOrder;
use app\index\model\CrmCustomer;
use Exception;
use think\Db;
class Els
{

    public static $crmOrderModel;
    public static $crmCustomerModel;


    public static function update(){
        $file = upload();
        apiReturn(200,'成功',$file);
    }
    /**
     * 导出excel录入sql
     **/
    public static function import($file,$table=''){
        self::$crmOrderModel=new CrmOrder();
        self::$crmCustomerModel =new CrmCustomer();
        $file =ROOT_PATH . 'public' . DS . 'uploads\\'. $file;
        if(!is_file($file)) return '文件地址错误:'.$file;
        echo '----解析文件中----';
        $type = pathinfo($file);
        $type = strtolower($type["extension"]);
        if ($type=='xlsx') {
            $type='Excel2007';
        }elseif($type=='xls') {
            $type = 'Excel5';
        }
        $objReader = \PHPExcel_IOFactory::createReader($type);
        $objPHPExcel = $objReader->load($file, $encode = 'utf-8');
        $excel_array = $objPHPExcel->getsheet(0)->toArray();
        $res = [];
        array_shift($excel_array);//去掉第一行，因为我上传的excel文件第一行为列名
        echo '----导入中----';
        if(!is_array($excel_array) || empty($excel_array)){
            return $res;
        }else{
           $data = self::processing($excel_array,$table);
        }
        if(is_string($data)) return $data;
        Db::startTrans();
        try{
            db($table)->insertAll($data);
            Db::commit();
            unlink($file);
        }catch (\Exception $e){
            Db::rollback();
            unlink($file);
            apiReturn(4001,$e->getMessage());
        }
    }

    /**
     * 处理导入的数据
     * $table
     * crm_manage管理
     * crm_client客户
     * crm_order客户订单
     **/
    protected static function processing($excel_array,$table=''){
        $res =[];
        switch ($table)
        {
            case 'crm_manage':
                //循环读取数据
                foreach ($excel_array as $key => $value) {
                    if((Db::name('crm_manage')->where(['username'=>$value[0],'name'=>$value[1]])->find())) continue;
                    $res[$key]['username'] = (string)$value[0];
                    $res[$key]['name'] = (string)$value[1];
                    $CrmGroupModel = new CrmGroup();
                    $groupid = $CrmGroupModel->addGroup($value[2]);
                    if($groupid)$res[$key]['group_id']=$groupid;
                    $res[$key]['password']= '9f866e655289cb219ade3747262eda5c08302349';
                    $res[$key]['zu_id']=1;
                    $res[$key]['status']=0;
                    if($value[8]=='否'){
                        $res[$key]['status']=1;
                    }
                }
                break;
            case 'crm_order':
                $crmManage = new CrmManage();
                $crmCustomer = new CrmCustomer();
                //循环读取数据
                foreach ($excel_array as $key => $value) {
                    $orders = self::$crmOrderModel->where('customer_id',(string)$value[1])->select();
                    if(count($orders)==0)continue;
                    foreach ($orders as $val){
                        if($val->total=='2980' && (string)$value[29]!=''){
                            self::$crmOrderModel->where('id',$val['id'])->update(['express_number'=>(string)$value[29],'order_status'=>5,'check_status'=>4]);
                        }elseif ($val->total=='2980' && (string)$value[29]==''){
                            self::$crmOrderModel->where('id',$val['id'])->update(['order_status'=>2,'check_status'=>2]);
                        }
                        if(strtotime($value[18])>1680278400){
                            $username = (string)$value[23];
                            if($username[0]==2){
                                $username = '70'.substr($username,1);
                            }
                            $Manage = $crmManage->where('username',$username)->find();
                            if(!$Manage){
                                dump('----未找到下单人--'.(string)$value[23]);
                                continue;
                            }
                            $customer = $crmCustomer->where('client_id',(string)$value[1])->find();
                            if($customer){
                                $crmCustomer->where('id',$customer['id'])->update(['manage_id'=>$Manage['username']]);
                            }else{
                                dump('----未找到客户--'.(string)$value[1]);
                            }
                        }
                    }



//                    $status =(string)$value[10];
//                    if($status=='等待支付'){
//                        $order_status = 1;
//                    }elseif($status=='已支付'){
//                        $order_status = 2;
//                    }elseif ($status=='待发货'){
//                        $order_status = 3;
//                    }elseif ($status=='已发货'){
//                        $order_status= 4;
//                    }elseif ($status=='交易成功'){
//                        $order_status = 5;
//                    }elseif($status=='部分退'){
//                        $order_status= 6;
//                    }elseif ($status=='拒收'){
//                        $order_status = 7;
//                    }elseif($status=='已作废'){
//                        $order_status = 8;
//                    }else{
//                        $order_status =0;
//                    }
//                    $order_type = 0;
//                    if((string)$value[12]=='正常单'){
//                        $order_type =1;
//                    }
//                    if((self::$crmOrderModel->where(['orderid'=>(string)$value[0]])->find())){
//                        self::$crmOrderModel->allowField(['order_type','order_status'])->save(['order_type'=>$order_type,'order_status'=>$order_status], ['orderid' => (string)$value[0]]);
//                        continue;
//                    }
//                    self::import_order_goods_money($value);
//                    $res[$key]['order_status'] = $order_status;
//                    $res[$key]['order_type'] = $order_type;
//                    $res[$key]['orderid'] = (string)$value[0];
//                    $res[$key]['customer_id'] = (string)$value[1];
//                    $res[$key]['telephone'] = (string)$value[3];
//                    $res[$key]['name'] = (string)$value[2];
//                    $res[$key]['phone'] = (string)$value[4];
//                    $string = explode('/',(string)$value[5]);
//                    $res[$key]['province'] = (string)$value[5];
//                    $res[$key]['city'] = (string)$value[5];
//                    $res[$key]['area'] = (string)$value[5];
//                    if(isset($string[0])){
//                        $res[$key]['province'] = $string[0];
//                    }
//                    if(isset($string[1])){
//                        $res[$key]['city'] = $string[1];
//                    }
//                    if(isset($string[2])){
//                        $res[$key]['area'] = $string[2];
//                    }
//                    $res[$key]['address'] = (string)$value[6];
//                    $res[$key]['postalcode'] = (string)$value[7];
//                    $res[$key]['pay_way'] = (string)$value[8];
//                    $res[$key]['pay_account'] = (string)$value[9];
//                    $res[$key]['total'] = (string)$value[13];
//                    $res[$key]['ding_price'] = (string)$value[14];
//                    $res[$key]['weishou_price'] = (string)$value[15];
//                    $res[$key]['drawback_price'] = (string)$value[16];
//                    $res[$key]['date'] = strtotime($value[18]);
//                    $res[$key]['shenhe_time'] = strtotime($value[19]);
//                    $res[$key]['fahuo_time'] = strtotime($value[20]);
//                    $res[$key]['qianshou_time'] = strtotime($value[21]);
//                    $res[$key]['manage_id'] = (string)$value[23];
//                    $res[$key]['check_manage'] = (string)$value[26];
//                    $res[$key]['express_number'] = (string)$value[29];
                }
                break;
            case 'crm_customer':
                //循环读取数据
                foreach ($excel_array as $key => $value) {
                    $phone =[(string)$value[4],(string)$value[5],(string)$value[6]];
                    $where['phone']= ['in',$phone];
                    $where['phone2']=['in',$phone];
                    $where['phone3']=['in',$phone];
                    if(self::$crmCustomerModel->where($where)->find()) continue;
                    $res['client_id'] = (string)$value[0];
                    if(self::$crmCustomerModel->where(['client_id'=>(string)$value[0]])->find()) continue;
                    $res['name'] = (string)$value[1];
                    $res['sex'] = (string)$value[2];
                    $res['birth'] = (string)$value[3];
                    $res['phone'] = (string)$value[4];
                    $res['phone2'] = (string)$value[5];
                    $res['phone3'] = (string)$value[6];
                   // $res[$key]['intention'] = (string)$value[7];
                   // $res[$key]['dengji'] = (string)$value[8];
                    $res['source'] = (string)$value[9];
                    $res['address'] = (string)$value[10];
                    $res['manage_id'] = (string)$value[11];
                    $res['total'] = (string)$value[14];
                    $res['weixin'] = (string)$value[18];
                    $res['email'] = (string)$value[20];
                    $res['date'] = strtotime($value[21]);
                    $res['children_name'] = (string)$value[26];
                    $res['grade'] = (string)$value[27];
                    //$res[$key]['school_way'] = (string)$value[28];
                   // $res[$key]['gohome_time'] = (string)$value[29];
                    //$res[$key]['score'] = (string)$value[30];
                    //$res[$key]['character'] = (string)$value[31];
                    $res['remark'] = (string)$value[32];
                    self::$crmCustomerModel->insert($res);
                }
                break;
            default:
                apiReturn(4001,'数据上传错误');
        }

        if($table=='crm_manage' || $table=='crm_order'){
            return $res;
        }else{
            return '完毕';
        }
    }

    /*
     * 修改客户状态
     * */
    public static function up_customer($excel_array){
        try{
            foreach ($excel_array as $v){
                $status = $v[8];
                $up=null;
                if($status=='B已加微信'){
                    $up = ['yaoyue_status'=>1];
                }elseif ($status=='A已成交正式课'){
                    $up = ['label'=>1];
                }elseif ($status=='C已接听未加V'){
                    $up = ['yaoyue_status'=>'2'];
                }elseif ($status=='D未接听'){
                    $up = ['yaoyue_status'=>'3'];
                }elseif ($status=='E已退公开课'){
                    $up = ['yaoyue_status'=>'4','is_refund'=>'1'];
                }elseif ($status=='F空号或非本人'){
                    $up = ['yaoyue_status'=>'5'];
                }elseif ($status=='G未付款数据'){
                    $up = $status;
                }elseif ($status=='H思维导图已成交'){
                    $up = $status;
                }
                if(is_string($up)){
                    $res  = self::$crmCustomerModel->where('client_id',$v[0])->find();
                    $res['remark'].=$up;
                    self::$crmCustomerModel->where('client_id',$v[0])->update(['remark'=>$up]);
                }
                if(is_array($up)){
                    self::$crmCustomerModel->where('client_id',$v[0])->update($up);
                }
            }
        }catch (Exception $exception){
            return $exception->getMessage();
        }

        return '已经修改完成';
    }

    /*
     * 处理导入的
     * crm订单商品表
     * 订单业绩表
     * */
    public static function import_order_goods_money($ordes){
        $CrmOrderGoodsModel = new CrmOrderGoods;
        $CrmOrderMoneyModel = new CrmOrderMoney;
        $goodsid = explode('，',(string)$ordes[34]);
        $goods = explode('，',(string)$ordes[35]);
        $OrderGoods = [];
        $CrmOrderMoney = [];
        $num=0;
        while ($num<count($goodsid)){
            $goodsid_part = explode('(',$goodsid[$num])[0];
            $goods_part = explode('(',$goods[$num])[0];
             array_push($OrderGoods,['order_id'=>(string)$ordes[0],'goods_id'=>$goodsid_part,'goods_name'=>$goods_part,'goods_caigou_price'=>(string)$ordes[37],'goods_price'=>(string)$ordes[38],'goods_number'=>(string)$ordes[36],'date'=>strtotime($ordes[32])]);
             array_push($CrmOrderMoney,['order_id'=>(string)$ordes[0],'manage_id'=>(string)$ordes[23],'money'=>(string)$ordes[13],'date'=>strtotime((string)$ordes[32])]);
            $num++;
        }
        $CrmOrderGoodsModel->add($OrderGoods,(string)$ordes[0]);
        $CrmOrderMoneyModel->add($CrmOrderMoney,(string)$ordes[0]);
    }
}